package com.wpzf.ndhc.controller;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.wpzf.common.core.web.domain.AjaxResult;
import com.wpzf.common.log.annotation.Log;
import com.wpzf.common.log.enums.BusinessType;
import com.wpzf.ndhc.domain.WpSjTbjbxxNd2024;
import com.wpzf.ndhc.service.WpzfNdhcGetNdDataService;
import com.wpzf.ndhc.utils.GTYUtil;
import com.wpzf.ndhc.utils.LogUtil;
import com.wpzf.ndhc.utils.MyObjectUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import org.sqlite.mc.SQLiteMCSqlCipherConfig;

import java.io.*;
import java.net.URL;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.*;
@RestController
@RequestMapping("/wpzfnddata")
public class WpzfNdhcGetNdDataController
{
    private final static ResourceBundle rb2 = ResourceBundle.getBundle("config/log");
    private final static String logUrl = rb2.getString("logToTxtUrl.ndhcInfoTranslate.receive2024GJJNdTbInfo");

    @Autowired
    WpzfNdhcGetNdDataService wpzfNdhcGetNdDataService;

    @Log(title = "获取2024年国家级图斑数据 给DB存储到数据库", businessType = BusinessType.OTHER)
    @RequestMapping(value = "/getTb", method = RequestMethod.POST)
    public AjaxResult getTb() throws IOException, SQLException
    {
        List<Map<String,Object>>  dblist=wpzfNdhcGetNdDataService.getDbInfo();
        String dbPassword="Gtdcy20240217";
        if(dblist!=null && dblist.size()>0)
        {
            for(int i=0;i<dblist.size();i++)
            {
                String dbPath=dblist.get(i).get("url").toString();
                String savePath = System.getProperty("user.dir") + "\\" + "gjjDBFile";
                String xzqdm=dblist.get(i).get("xzqdm").toString();
                int dbcount=Integer.parseInt(dblist.get(i).get("count").toString());
                String fileName = dbPath.substring(dbPath.lastIndexOf("/") + 1);
                dbPath = downloadByIO(dbPath, savePath, fileName);
                File dbfile = new File(dbPath);

                if(dbfile.exists())
                {
                    LogUtil.logToTxt(xzqdm+"db路径:"+dbPath, logUrl);
                    LogUtil.logToTxt(xzqdm+"db密码:"+dbPassword, logUrl);



                    try {
                        String fileurl = "jdbc:sqlite:" + dbPath;
                        Connection conn = null;
                        conn = SQLiteMCSqlCipherConfig.getV3Defaults().withKey(dbPassword).build().createConnection(fileurl);
                        Statement statement = conn.createStatement();
                        statement.setQueryTimeout(30);  // set timeout to 30 sec.
                        List<WpSjTbjbxxNd2024> tbxxbNdDTOList = new ArrayList<>();

                        //入库
                        ResultSet rs = statement.executeQuery("select * from tbjbxx");
                        while (rs.next()) {
                            WpSjTbjbxxNd2024 model = new WpSjTbjbxxNd2024();
                            model.setTbbsm(MyObjectUtil.objectIsNullToString(rs.getString("tbbsm")).toString());
                            model.setXzqdm(MyObjectUtil.objectIsNullToString(rs.getString("xzqdm")).toString());
                            model.setXzqmc(MyObjectUtil.objectIsNullToString(rs.getString("xzqmc")).toString());
                            model.setXzdm(MyObjectUtil.objectIsNullToString(rs.getString("xzdm")).toString());
                            model.setXzmc(MyObjectUtil.objectIsNullToString(rs.getString("xzmc")).toString());
                            model.setJcbh(MyObjectUtil.objectIsNullToString(rs.getString("jcbh")).toString());
                            model.setTblx(MyObjectUtil.objectIsNullToString(rs.getString("tblx")));
                            model.setTbtz(MyObjectUtil.objectIsNullToString(rs.getString("tbtz")).toString());
                            model.setHsx(MyObjectUtil.objectIsNullToString(rs.getString("hsx")).toString());
                            model.setJcmj(MyObjectUtil.objectIsNullToDouble(rs.getString("jcmj")));
                            model.setJctqmj(MyObjectUtil.objectIsNullToDouble(rs.getString("jctqmj")));
                            model.setBz(MyObjectUtil.objectIsNullToString(rs.getString("bz")).toString());
                            model.setDlrdnydmj(MyObjectUtil.objectIsNullToDouble(rs.getString("dlrdnydmj")));
                            model.setDlrdgdmj(MyObjectUtil.objectIsNullToDouble(rs.getString("dlrdgdmj")));
                            model.setQtnydmj(MyObjectUtil.objectIsNullToDouble(rs.getString("qtnydmj")));
                            model.setDlrdjsydmj(MyObjectUtil.objectIsNullToDouble(rs.getString("dlrdjsydmj")));
                            model.setDlrdwlydmj(MyObjectUtil.objectIsNullToDouble(rs.getString("dlrdwlydmj")));
                            model.setNzy(MyObjectUtil.objectIsNullToString(rs.getString("nzy")).toString());
                            model.setZjgxj(MyObjectUtil.objectIsNullToString(rs.getString("zjgxj")).toString());
                            model.setXxydmj(MyObjectUtil.objectIsNullToString(rs.getString("xxydmj")).toString());
                            model.setWly(MyObjectUtil.objectIsNullToString(rs.getString("wly")).toString());
                            model.setZjgcj(MyObjectUtil.objectIsNullToString(rs.getString("zjgcj")).toString());
                            model.setTdzz(MyObjectUtil.objectIsNullToString(rs.getString("tdzz")).toString());
                            model.setGkfq(MyObjectUtil.objectIsNullToString(rs.getString("gkfq")).toString());
                            model.setSsnydmj(MyObjectUtil.objectIsNullToString(rs.getString("ssnydmj")).toString());
                            model.setLsydmj(MyObjectUtil.objectIsNullToString(rs.getString("lsydmj")).toString());
                            model.setFist_kyq(MyObjectUtil.objectIsNullToString(rs.getString("fist_kyq")).toString());
                            model.setJbntmj(MyObjectUtil.objectIsNullToDouble(rs.getString("jbntmj")));
                            model.setStbhhxmj(MyObjectUtil.objectIsNullToDouble(rs.getString("stbhhxmj")));
                            model.setSxsj(MyObjectUtil.objectIsNullToString(rs.getString("sxsj")).toString());
                            model.setHsxdz(MyObjectUtil.objectIsNullToString(rs.getString("hsxdz")).toString());
                            model.setHsxdz_tif(MyObjectUtil.objectIsNullToString(rs.getString("hsxdz_tif")).toString());
                            model.setHyjg_url(MyObjectUtil.objectIsNullToString(rs.getString("hyjg_url")).toString());
                            model.setLongitude(MyObjectUtil.objectIsNullToDouble(rs.getString("longitude")));
                            model.setLatitude(MyObjectUtil.objectIsNullToDouble(rs.getString("latitude")));
                            model.setShape(MyObjectUtil.objectIsNullToString(rs.getString("shape")).toString());
                            model.setMaxx(MyObjectUtil.objectIsNullToDouble(rs.getString("maxx")));
                            model.setMinx(MyObjectUtil.objectIsNullToDouble(rs.getString("minx")));
                            model.setMaxy(MyObjectUtil.objectIsNullToDouble(rs.getString("maxy")));
                            model.setMiny(MyObjectUtil.objectIsNullToDouble(rs.getString("miny")));
                            model.setSjdrly(MyObjectUtil.objectIsNullToString(rs.getString("sjdrly")).toString());
                            model.setCreatetime(MyObjectUtil.objectIsNullToString(rs.getString("createtime")).toString());
                            model.setModifytime(MyObjectUtil.objectIsNullToString(rs.getString("modifytime")).toString());
                            model.setPch("年度卫片");
                            model.setOp_time((new Date()).toString());
                            tbxxbNdDTOList.add(model);
                        }
                        LogUtil.logToTxt(xzqdm+"图斑数量:" + tbxxbNdDTOList.size(), logUrl);

                        deleteTbxxbNd2024(tbxxbNdDTOList);

                        Integer b = saveTbxxbNd2024(tbxxbNdDTOList);
                        if (b > 0)
                        {
                            if(dbcount==b)
                            {
                                LogUtil.logToTxt(xzqdm+"年度图斑导入成功", logUrl);
                            }
                            else
                            {
                                LogUtil.logToTxt(xzqdm+"年度图斑导入成功,原始数量"+dbcount+",导入数量："+b, logUrl);
                            }
                        }
                        else
                        {
                            LogUtil.logToTxt(xzqdm+"年度图斑导入失败", logUrl);
                        }
                    } catch (Exception e) {
                        LogUtil.logToTxt("入库失败", logUrl);
                        System.err.println(e.getMessage());
                        return AjaxResult.error("2024年国家级图斑导入失败",e.toString());
                    }
                }
                else
                {
                    LogUtil.logToTxt(xzqdm+"DB包下载失败", logUrl);
                }
            }
        }
        return AjaxResult.success("DB包图斑存储完成，详细信息查看日志");
    }
    @Transactional
    public Integer deleteTbxxbNd2024(List<WpSjTbjbxxNd2024> wpSjTbjbxxNd2024List) {
        Integer rest = 0;
        for (WpSjTbjbxxNd2024 wpSjTbjbxxNd2024 : wpSjTbjbxxNd2024List) {
            rest += wpzfNdhcGetNdDataService.deleteTbxxbNd2024(wpSjTbjbxxNd2024);
        }
        return rest;
    }
    @Transactional
    public Integer saveTbxxbNd2024(List<WpSjTbjbxxNd2024> wpSjTbjbxxNd2024List) {
        Integer rest = 0;
        for (WpSjTbjbxxNd2024 wpSjTbjbxxNd2024 : wpSjTbjbxxNd2024List) {
            rest += wpzfNdhcGetNdDataService.saveTbxxbNd2024(wpSjTbjbxxNd2024);
        }
        return rest;
    }
    public static String downloadByIO(String url, String saveDir, String fileName) {
        BufferedOutputStream bos = null;
        InputStream is = null;
        try {
            byte[] buff = new byte[8192];
            is = new URL(url).openStream();
            File file = new File(saveDir, fileName);
            file.getParentFile().mkdirs();
            bos = new BufferedOutputStream(new FileOutputStream(file));
            int count = 0;
            while ((count = is.read(buff)) != -1) {
                bos.write(buff, 0, count);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (bos != null) {
                try {
                    bos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return saveDir + "\\" + fileName;
    }


    @Log(title = "通过图斑信息获取地块基本信息并存储到地块表", businessType = BusinessType.OTHER)
    @RequestMapping(value = "/getDk", method = RequestMethod.POST)
    public AjaxResult getDk() throws IOException, SQLException
    {
        GTYUtil gtyUtil=new GTYUtil();
        List<WpSjTbjbxxNd2024>  tbList=wpzfNdhcGetNdDataService.getNdTb();
        if(tbList!=null && tbList.size()>0)
        {
           for(int i=0;i<tbList.size();i++)
           {
               WpSjTbjbxxNd2024 wpSjTbjbxxnd2024=tbList.get(i);
               String tbbsm=wpSjTbjbxxnd2024.getTbbsm(); //tbbsm
               String tbbh=wpSjTbjbxxnd2024.getJcbh(); //tbbh
               String xzqdm=wpSjTbjbxxnd2024.getXzqdm();//xzqdm

               //国土云接口获取地块 保存到地块表
               Map<String,String> map=new HashMap<>();
               map.put("xzqdm",xzqdm);
               map.put("tbbh",tbbh);
               int k=wpzfNdhcGetNdDataService.deleteDkxxbNd2024(map);

               List<Map<String,Object>>  dklist=new ArrayList<>(); //从国土云获取的地块列表
               //String info=gtyUtil.getDkInfo("360602","P1BH296");

               String info=gtyUtil.getDkInfo(xzqdm,tbbh);
               JSONObject obj = JSONObject.parseObject(info);
               if (obj.get("data")!= null) {
                   JSONArray dkObject= obj.getJSONArray("data");
                   if (dkObject != null && dkObject.size() > 0)
                   {
                      for(int mn=0;mn<dkObject.size();mn++)
                      {
                          JSONObject dkinfo = dkObject.getJSONObject(mn);
                          Map<String,Object> dk=new HashMap<>();//获取地块 信息并存储
                          dk.put("xzqdm",dkinfo.get("xzqdm"));
                          dk.put("dkbh",dkinfo.get("tbbh"));
                          dk.put("pdlx",dkinfo.get("tblx"));
                          dk.put("xmc",dkinfo.get("xmc"));
                          dk.put("pch","年度卫片");

                          dk.put("dkmj",dkinfo.get("sjtbmj"));
                          dk.put("gdmj",dkinfo.get("gdmj"));
                          dk.put("qtnydmj",dkinfo.get("qtnydmj"));
                          dk.put("jsydmj",dkinfo.get("jsydmj"));
                          dk.put("wlydmj",dkinfo.get("wlydmj"));
                          dk.put("sfbgjsyd",dkinfo.get("sfjsyd"));
                          dk.put("nodeid",dkinfo.get("nodeid"));

                          dk.put("tbbsm",dkinfo.get("tbbsm"));
                          dk.put("op_time",(new Date()).toString());
                          dk.put("bsm",UUID.randomUUID().toString().replace("-", ""));
                          dklist.add(dk);
                      }
                   }
                   for(int j=0;j<dklist.size();j++)
                   {
                       Map<String,Object> dk=dklist.get(j);//获取地块 信息并存储
                       k=wpzfNdhcGetNdDataService.saveDkjbxxbNd2024(dk);  //地块存储起来
                   }
               }
           }
            return AjaxResult.error("地块获取为空");
        }
        else {
            return AjaxResult.error("地块获取为空");
        }
    }
}
